﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DataServer.DTO;
using System.Data;

namespace DataServer.DAO
{
    public class PhongBanDAO : DataProvider
    {
        public List<string> GetColName()
        {
            List<string> cols = new List<string>();
            string query = "SELECT * FROM PHONGBAN";
            DataTable dt = new DataTable();
            dt = executeQuery(query);
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                cols.Add(dt.Columns[i].ToString());
            }
            return cols;
        }

        public List<PhongBanDTO> LayDanhSachPhongBan()
        {

            string query = "SELECT * FROM PHONGBAN ORDER BY MAPHG ASC";
            //adapter = new OleDbDataAdapter(query, connection);
            //DataTable dt = new DataTable();
            //adapter.Fill(dt);
            DataTable dt = new DataTable();
            dt = executeQuery(query);
            List<PhongBanDTO> result = new List<PhongBanDTO>();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                PhongBanDTO pb = (PhongBanDTO)GetDataFromDataRow(dt, i);
                result.Add(pb);
            }

            return result;
        }

        public PhongBanDTO getPhongBan(string id)
        {
            string query = "SELECT * FROM PHONGBAN WHERE MAPHG=" + id;
            //adapter = new OleDbDataAdapter(query, connection);
            //DataTable dt = new DataTable();
            //adapter.Fill(dt);
            DataTable dt = new DataTable();
            dt = executeQuery(query);
            List<PhongBanDTO> result = new List<PhongBanDTO>();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                PhongBanDTO pb = (PhongBanDTO)GetDataFromDataRow(dt, i);
                result.Add(pb);
            }

            return result[0];
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            PhongBanDTO pb = new PhongBanDTO();
            pb.MAPHG = (string)dt.Rows[i]["MAPHG"];
            pb.TenPHG = (string)dt.Rows[i]["TenPHG"];
            pb.TRPHG = (int)dt.Rows[i]["TRPHG"];
            pb.NG_NhanChuc = (string)dt.Rows[i]["NG_NhanChuc"];
            
            return pb;
        }

        public bool ThemPhongBan(PhongBanDTO pb)
        {
            string query = "INSERT INTO PHONGBAN(MAPHG, TenPHG, TRPHG, NG_NhanChuc) VALUES ('"
                + pb.MAPHG + "', N'"
                + pb.TenPHG + "', '"
                + pb.TRPHG + "', '"
                + pb.NG_NhanChuc + "')";

            try
            {
                executeNonQuery(query);
                return true;
            }
            catch (System.Exception e)
            {
                return false;
            }
        }

        public bool XoaPhongBan(string maPB)
        {
            string query = "DELETE FROM PHONGBAN WHERE MAPHG ='" + maPB + "'";

            try
            {
                executeNonQuery(query);
                return true;
            }
            catch (System.Exception e)
            {
                return false;
            }
        }

        public bool SuaPhongBan(string id, PhongBanDTO pb)
        {
            string query = "UPDATE PHONGBAN SET TenPHG = N'" + pb.TenPHG
                + "', MAPHG = '" + pb.MAPHG
                + "', TRPHG = '" + pb.TRPHG
                + "', NG_NhanChuc = '" + pb.NG_NhanChuc
                + "' WHERE MAPHG = '" + id + "'";

            try
            {
                executeNonQuery(query);
                return true;
            }
            catch (System.Exception e)
            {
                return false;
            }
        }

    }
}